﻿/*********************************************
 创建者：  dmhai
 创建时间：2017-04-05
 功能说明：小品类平均单价
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using PlanAPI.model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;

namespace PlanAPI
{
    [Description("小品类平均单价(全国/月)")]
    public class PLItemPricePeriodHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 获取[全国小品类平均单价]列表 GetAreaItemPriceList
        [MustLogin]
        [Description("获取[全国小品类平均单价]列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLItemPricePeriodModel.Area.ListModel>>))]
        public ApiResult GetAreaItemPriceList([ParamDescription("商品区域")] string ParentAreaID = "",
          [ParamDescription("类别")] string KindID = "",
          [ParamDescription("系列")] string SeriesID = "",
          [ParamDescription("商品层")] string MaterialProperty = "",
          [ParamDescription("品类")] string ModelID = "",
          [ParamDescription("小品类")] string ItemID = "",
          [ParamDescription("页码")] int Page = 1,
          [ParamDescription("页数")] int Size = 10)
        {
            #region 没企划信息就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.全国;


            //整个查询分两步走：①先查询组信息，构造where条件；②根据where查询数据

            #region 先根据前端界面条件查询组信息,构造where条件

            //按当前企划的年月查询
            string groupWhereStr = $"where p.CompanyID = '{UserInfo.UserCompanyId}' and p.Yearv = {plan.Yearv} and p.Monthv = {plan.Monthv} ";
            //加入AreaOrShop条件
            groupWhereStr += $" and p.AreaOrShop = {AreaOrShop} ";
            //加入前台传来的参数
            groupWhereStr += string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and p.ParentAreaID = '{ParentAreaID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and p.MaterialProperty = '{MaterialProperty}'";
            groupWhereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and p.KindID = '{KindID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and p.SeriesID = '{SeriesID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(ModelID) ? "" : $" and p.ModelID = '{ModelID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(ItemID) ? "" : $" and p.ItemID = '{ItemID}'";
            //特殊规则
            //1、不要推品
            groupWhereStr += " and p.SeriesID != '000005'";

            //获取分组条数,将返回到前端做分页显示
            string countSql = GetSqlWithGroup("count(1)", groupWhereStr);
            var countCmd = Helper.GetSqlStringCommand(countSql);
            var groupCount = Convert.ToInt32(Helper.ExecuteScalar(countCmd));

            //这里按组分页，得到需要的信息并且拼接成where条件
            var groupSql = GetSqlWithGroup("*", groupWhereStr);
            string whereSql = GetWhereSql(Page, Size, groupSql);

            #endregion


            #region 根据where查询数据
            var result = PaperData<IGrouping<string, PLItemPricePeriodModel.Area.ListModel>>.Create(groupCount, Size, Page);
            //where条件不存在就停止，不然会直接读取整个表，内存会爆掉
            if (string.IsNullOrWhiteSpace(whereSql))
            {
                result.Items = new List<IGrouping<string, PLItemPricePeriodModel.Area.ListModel>>();
                return Success(result);
            }

            var listSql = @"SELECT *
FROM (	
select kind.KindName,series.SeriesName,model.ModelName,item.ItemName,
p.CompanyID, p.Yearv, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID, p.MaterialProperty, p.ModelID, p.ItemID, p.PriceName, p.MoveQtyRate, p.SupplyQtyRate, p.SaleQtyRate, p.MSRate, p.StandAmountRate, p.PtStandAmountRate, p.CkPtStandAmountRate, p.AvgPrice, 
--p.Remark, p.CheckState, p.Checker, p.MofifyDTM, p.AddFlag, p.LoadTime, p.EtlDate,, p.AutoStandAmountRate, p.CalcStandAmountRate, p.AvgMSRate, p.SaleQty, p.SupplyQty, p.MoveQty
ROW_NUMBER()OVER(ORDER BY Yearv,Monthv) AS rowIndex
FROM ngoods.ngoods_PL_Item_PricePeriod as p
left join nods.nods_erp_sd_mat_kind as kind on p.ExtrSource = kind.ExtrSource and p.KindID = kind.KindID
left join nods.nods_erp_sd_mat_series as series on p.ExtrSource = series.ExtrSource and p.SeriesID = series.SeriesID
left join nods.nods_erp_sd_mat_model as model on p.ExtrSource = model.ExtrSource and p.ModelID = model.ModelID
left join nods.nods_erp_SD_Mat_Item as item on p.ExtrSource = item.ExtrSource and p.ItemID = item.ItemID
{0}
order by p.Yearv,p.Monthv,p.KindID,p.SeriesID,p.MaterialProperty,p.ModelID,p.ItemID,cast(p.PriceName as decimal(8,4))
) table1 ";

            listSql = string.Format(listSql, whereSql);
            //注意！这里不再分页
            var cmd = Helper.GetSqlStringCommand(listSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<PLItemPricePeriodModel.Area.ListModel>.ConvertToModel(dt);
            var resultList = list.GroupBy(p => p.Yearv + "_" + p.Monthv + "_" + p.MaterialProperty + "_" + p.KindID + "_" + p.SeriesID + "_" + p.ModelID + "_" + p.ItemID)
                .ToList();

            //分组
            result.Items = resultList;
            return Success(result);
            #endregion

        }
        #endregion

        #region 获取[单店小品类平均单价]列表 GetShopItemPriceList
        [MustLogin]
        [Description("获取[单店小品类平均单价]列表")]
        [ReturnType(typeof(PaperData<IGrouping<string, PLItemPricePeriodModel.Shop.ListModel>>))]
        public ApiResult GetShopItemPriceList([ParamDescription("商品区域")] string ParentAreaID = "",
          [ParamDescription("店铺")] string ShopID = "",
          [ParamDescription("类别")] string KindID = "",
          [ParamDescription("系列")] string SeriesID = "",
          [ParamDescription("商品层")] string MaterialProperty = "",
          [ParamDescription("品类")] string ModelID = "",
          [ParamDescription("小品类")] string ItemID = "",
          [ParamDescription("页码")] int Page = 1,
          [ParamDescription("页数")] int Size = 10)
        {

            #region 没企划信息就直接返回

            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan == null)
            {
                return Error("找不到当前的企划信息");
            }

            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.区域;

            //整个查询分两步走：①先查询组信息，构造where条件；②根据where查询数据

            #region 先根据前端界面条件查询组信息,构造where条件

            //按当前企划的年月查询
            string groupWhereStr = $"where p.CompanyID = '{UserInfo.UserCompanyId}' and  p.Yearv = {plan.Yearv} and p.Monthv = {plan.Monthv} ";
            //加入AreaOrShop条件
            groupWhereStr += $" and p.AreaOrShop = {AreaOrShop} ";
            //加入前台传来的参数
            groupWhereStr += string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and p.ParentAreaID = '{ParentAreaID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(MaterialProperty) ? "" : $" and p.MaterialProperty = '{MaterialProperty}'";
            groupWhereStr += string.IsNullOrWhiteSpace(KindID) ? "" : $" and p.KindID = '{KindID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(SeriesID) ? "" : $" and p.SeriesID = '{SeriesID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(ModelID) ? "" : $" and p.ModelID = '{ModelID}'";
            groupWhereStr += string.IsNullOrWhiteSpace(ItemID) ? "" : $" and p.ItemID = '{ItemID}'";

            //特殊规则
            //1、不要推品
            groupWhereStr += " and p.SeriesID != '000005'";

            //获取分组条数,将返回到前端做分页显示
            string countSql = GetSqlWithGroup("count(1)", groupWhereStr);
            var countCmd = Helper.GetSqlStringCommand(countSql);
            var groupCount = Convert.ToInt32(Helper.ExecuteScalar(countCmd));

            //这里按组分页，得到需要的信息并且拼接成where条件
            var groupSql = GetSqlWithGroup("*", groupWhereStr);
            string whereSql = GetWhereSql(Page, Size, groupSql);

            #endregion

            #region 根据where查询数据
            var result = PaperData<IGrouping<string, PLItemPricePeriodModel.Shop.ListModel>>.Create(groupCount, Size, Page);
            //where条件不存在就停止，不然会直接读取整个表，内存会爆掉
            if (string.IsNullOrWhiteSpace(whereSql))
            {
                result.Items = new List<IGrouping<string, PLItemPricePeriodModel.Shop.ListModel>>();
                return Success(result);
            }

            var listSql = @"SELECT *
FROM (	
select kind.KindName,series.SeriesName,model.ModelName,item.ItemName,shop.ShopName,
p.CompanyID, p.Yearv, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.ShopID, p.KindID, p.SeriesID, p.MaterialProperty, p.ModelID, p.ItemID, p.PriceName, p.MoveQtyRate, p.SupplyQtyRate, p.SaleQtyRate, p.MSRate, p.StandAmountRate, p.PtStandAmountRate, p.CkPtStandAmountRate, p.AvgPrice, 
--p.Remark, p.CheckState, p.Checker, p.MofifyDTM, p.AddFlag, p.LoadTime, p.EtlDate,, p.AutoStandAmountRate, p.CalcStandAmountRate, p.AvgMSRate, p.SaleQty, p.SupplyQty, p.MoveQty
ROW_NUMBER()OVER(ORDER BY Yearv,Monthv) AS rowIndex
FROM ngoods.ngoods_PL_Item_PricePeriod as p
left join nods.nods_erp_sd_mat_kind as kind on p.ExtrSource = kind.ExtrSource and p.KindID = kind.KindID
left join nods.nods_erp_sd_mat_series as series on p.ExtrSource = series.ExtrSource and p.SeriesID = series.SeriesID
left join nods.nods_erp_sd_mat_model as model on p.ExtrSource = model.ExtrSource and p.ModelID = model.ModelID
left join nods.nods_erp_SD_Mat_Item as item on p.ExtrSource = item.ExtrSource and p.ItemID = item.ItemID
left join ndw.ndw_Shop as shop on p.CompanyID = shop.CompanyID and p.ShopID = shop.ShopID
{0}
order by p.Yearv,p.Monthv,p.ParentAreaID,p.KindID,p.SeriesID,p.MaterialProperty,p.ModelID,p.ItemID,cast(p.PriceName as decimal(8,4))
) table1 ";

            listSql = string.Format(listSql, whereSql);
            //注意！这里不再分页
            var cmd = Helper.GetSqlStringCommand(listSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<PLItemPricePeriodModel.Shop.ListModel>.ConvertToModel(dt);
            var resultList = list.GroupBy(p => p.Yearv + "_" + p.Monthv + "_" + p.ParentAreaID + "_" + p.MaterialProperty + "_" + p.KindID + "_" + p.SeriesID + "_" + p.ModelID + "_" + p.ItemID)
                .ToList();

            //分组
            result.Items = resultList;
            return Success(result);
            #endregion
        }
        #endregion



        
        #region 修改[全国小品类平均单价]数据 UpdateAreaItemPrice
        [MustLogin]
        [RoleBc]
        [JsonType]
        [Description("修改[全国小品类平均单价]")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateAreaItemPrice(PLItemPricePeriodModel.Area.UpdateModelList list)
        {
            if (list?.List == null || list.List.Count == 0)
            {
                return Error("参数获取失败，请刷新重试");
            }

            #region 校验输入的值是否正确
            //确认值，可手动修改,系统的小品类下所有的价格带的相加是100%
            var ckSum = list.List.GroupBy(p => new { p.Yearv, p.Monthv, p.KindID, p.SeriesID, p.MaterialProperty, p.ModelID, p.ItemID })
                .Select(p => p.Sum(i => i.CkPtStandAmountRate))
                .FirstOrDefault();
            if (ckSum > 100 || ckSum < 0)
            {
                return Error($"确认值之和必须在[0,100]区间内，当前为：{ckSum}%");
            }
            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.全国;

            var cmdList = new List<CommandInfo>();
            try
            {

                #region 修改确认值数据

                decimal avgPriceSum = 0;
                foreach (var item in list.List)
                {
                    decimal price = decimal.Parse(item.PriceName);
                    avgPriceSum += price * item.CkPtStandAmountRate / 100;
                }

                var model = list.List[0];
                //先修改平均金额
                string sql = $@"UPDATE ngoods.ngoods_PL_Item_PricePeriod 
SET AvgPrice = {avgPriceSum}
WHERE
CompanyID = '{UserInfo.UserCompanyId}' and 
AreaOrShop = {AreaOrShop} and Yearv = {model.Yearv} and Monthv = {model.Monthv} and 
KindID = '{model.KindID}' and SeriesID = '{model.SeriesID}' and MaterialProperty = '{model.MaterialProperty}' and 
ModelID = '{model.ModelID}' and ItemID = '{model.ItemID}' ";

                cmdList.Add(new CommandInfo(sql, null));
                //然后修改确认值
                foreach (var item in list.List)
                {
                    decimal ckPtStandAmountRate = item.CkPtStandAmountRate / 100;

                    sql = $@"UPDATE ngoods.ngoods_PL_Item_PricePeriod 
SET CkPtStandAmountRate = {ckPtStandAmountRate}, CheckState = 1 
WHERE
CompanyID = '{UserInfo.UserCompanyId}' and 
AreaOrShop = {AreaOrShop} and Yearv = {item.Yearv} and Monthv = {item.Monthv} and 
KindID = '{item.KindID}' and SeriesID = '{item.SeriesID}' and MaterialProperty = '{item.MaterialProperty}' and 
ModelID = '{item.ModelID}' and ItemID = '{item.ItemID}' and PriceName = '{item.PriceName}'  ";

                    cmdList.Add(new CommandInfo(sql, null));
                }
                #endregion

                //修改
                int n = Helper.ExecuteSqlTran(cmdList);
                if (n <= 0)
                {
                    return Error("修改失败，请刷新页面重试");
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 修改[区域小品类平均单价]数据 UpdateShopItemPrice
        [MustLogin]
        [RoleBc]
        [JsonType]
        [Description("修改[区域小品类平均单价]")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult UpdateShopItemPrice(PLItemPricePeriodModel.Shop.UpdateModelList list)
        {
            if (list?.List == null || list.List.Count == 0)
            {
                return Error("参数获取失败，请刷新重试");
            }

            #region 校验输入的值是否正确
            //确认值，可手动修改,系统的小品类下所有的价格带的相加是100%
            var ckSum = list.List.GroupBy(p => new { p.Yearv, p.Monthv,p.ParentAreaID, p.MaterialProperty, p.KindID, p.SeriesID, p.ModelID, p.ItemID })
                    .Select(p => p.Sum(i => i.CkPtStandAmountRate))
                    .FirstOrDefault();
            if (ckSum > 100 || ckSum < 0)
            {
                return Error($"确认值之和必须在[0,100]区间内，当前为：{ckSum}%");
            }

            #endregion

            int AreaOrShop = (int)AreaOrShopEnum.区域;

            var cmdList = new List<CommandInfo>();
            try
            {
                foreach (var item in list.List)
                {
                    decimal ckPtStandAmountRate = item.CkPtStandAmountRate / 100;
                    string sql = $@"UPDATE ngoods.ngoods_PL_Item_PricePeriod 
SET CkPtStandAmountRate = {ckPtStandAmountRate},
CheckState = 1 
WHERE  
CompanyID = '{UserInfo.UserCompanyId}' and 
AreaOrShop = {AreaOrShop} and Yearv = {item.Yearv} and Monthv = {item.Monthv} and 
ParentAreaID = '{item.ParentAreaID}' and MaterialProperty = '{item.MaterialProperty}' and 
KindID = '{item.KindID}' and SeriesID = '{item.SeriesID}' and ModelID = '{item.ModelID}' and 
ItemID = '{item.ItemID}' and PriceName = '{item.PriceName}' ";

                    cmdList.Add(new CommandInfo(sql, null));
                }

                //修改
                int n = Helper.ExecuteSqlTran(cmdList);
                if (n <= 0)
                {
                    return Error("修改失败，请刷新页面重试");
                }
                return Success(0);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion



        #region 返回数据总条数 GetRecordsCount
        /// <summary>
        /// 返回数据总条数(通用方法)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private int GetRecordsCount(string sql)
        {
            try
            {
                var n = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(n));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion

        #region 查询数据库并返回DataTable GetDataTable
        /// <summary>
        /// 查询数据库并返回DataTable(通用方法)
        /// </summary>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        private DataTable GetDataTable(int? Page, int? Size, string sql)
        {
            try
            {
                if (Page != null && Size != null)
                {
                    sql += string.Format(@" where rowIndex BETWEEN ({0}-1)*{1}+1 AND {0}*{1} ", Page, Size);
                }
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }
        #endregion


        #region 获取分组SQL之后的语句 GetSqlWithGroup

        private string GetSqlWithGroup(string field, string where)
        {
            //注：同是小品类下，各个步骤之间的区别是有没有用某字段，而没有用那个字段时，它为null，不影响分组，所以分组语句可以共用
            //周不要加入分组
            var sqlWithGroup = @"select {0} from (
    select  ROW_NUMBER()OVER(ORDER BY p.CompanyID, p.Yearv, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.MaterialProperty, p.ShopID, p.KindID, p.SeriesID,p.ModelID,p.ItemID) AS rowIndex, 
    count(SeriesID) as RowCount,p.CompanyID, p.Yearv, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.MaterialProperty, p.ShopID, p.KindID, p.SeriesID,p.ModelID,p.ItemID
    FROM ngoods.ngoods_PL_Item_PricePeriod p
    left join dw.dw_shop shop on p.CompanyID = shop.CompanyID and p.ShopID = shop.ShopID
    {1}
    group by p.CompanyID, p.Yearv, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.MaterialProperty, p.ShopID, p.KindID, p.SeriesID,p.ModelID,p.ItemID
    order by p.CompanyID, p.Yearv, p.Monthv, p.AreaOrShop, p.ParentAreaID, p.MaterialProperty, p.ShopID, p.KindID, p.SeriesID,p.ModelID,p.ItemID
) table1 ";

            return string.Format(sqlWithGroup, field, where);
        }
        #endregion

        #region 获取分组之后的数据并转换成where条件

        /// <summary>
        /// 获取分组之后的数据并转换成where条件
        /// </summary>
        /// <param name="page">页数</param>
        /// <param name="size">每页组数</param>
        /// <param name="groupSql"></param>
        /// <returns></returns>
        private string GetWhereSql(int page, int size, string groupSql)
        {
            //先拿到分组数据，得到所需的字段数据
            if (page != 0 && size != 0)
            {
                groupSql += string.Format($@" where rowIndex BETWEEN ({page}-1)*{size}+1 AND {page}*{size} ");
            }
            var cmd = Helper.GetSqlStringCommand(groupSql);
            var groupDataTable = Helper.ExecuteDataTable(cmd);
            var groupList = ModelConvertHelper<PLItemPricePeriodModel.GroupModel>.ConvertToModel(groupDataTable);

            //开始拼接条件
            StringBuilder sb = new StringBuilder($" where p.CompanyID = '{UserInfo.UserCompanyId}' and");

            for (int i = 0; i < groupList.Count; i++)
            {
                var model = groupList[i];

                sb.Append($" ( p.CompanyID='{model.CompanyID}' and p.Yearv={model.Yearv} and p.Monthv={model.Monthv} and p.AreaOrShop={model.AreaOrShop} ");

                if (!string.IsNullOrWhiteSpace(model.ParentAreaID))
                {
                    sb.Append($" and p.ParentAreaID='{model.ParentAreaID}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.MaterialProperty))
                {
                    sb.Append($" and p.MaterialProperty='{model.MaterialProperty}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.ShopID))
                {
                    sb.Append($" and p.ShopID='{model.ShopID}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.KindID))
                {
                    sb.Append($" and p.KindID='{model.KindID}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.SeriesID))
                {
                    sb.Append($" and p.SeriesID='{model.SeriesID}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.ModelID))
                {
                    sb.Append($" and p.ModelID='{model.ModelID}' ");
                }

                if (!string.IsNullOrWhiteSpace(model.ItemID))
                {
                    sb.Append($" and p.ItemID='{model.ItemID}' ");
                }

                sb.Append(")");

                if (i != groupList.Count - 1)
                {
                    sb.Append(" or\r\n ");
                }
            }
            var result = sb.ToString().Trim("and".ToCharArray());
            return result;
        }
        #endregion


        enum AreaOrShopEnum
        {
            全国 = 0,
            区域 = 1
        }      
    }
}
